package com.nit.bike.mapper.basic;

import com.alibaba.fastjson.JSONObject;
import com.nit.bike.dto.basic.UserDTO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

public interface StatisticsMapper {

    /**
     * 查询用户总数
     */
    @Select("SELECT COUNT(*) FROM basic_user " +
            "WHERE role_id = 2 AND user_state = 0"
    )
    Integer getUserTotal() throws Exception;

    /**
     * 查询用户增长
     */
    @Select("SELECT COUNT(*) FROM basic_user " +
            "WHERE role_id = 2 AND user_state = 0 " +
            "AND gmt_create BETWEEN #{beg} AND #{end}"
    )
    Integer getUserIncrease(@Param("beg") String beg,
                            @Param("end") String end) throws Exception;

    /**
     * 查询车辆信息
     */
    @Select("SELECT bike_state AS `type`, COUNT(*) AS `value` " +
            "FROM basic_bike " +
            "GROUP BY bike_state")
    List<JSONObject> getBikeStats() throws Exception;

    /**
     * 充值和消费总计
     */
    @Select("SELECT record_type AS `type`, SUM(money) AS `value` " +
            "FROM basic_card_record " +
            "GROUP BY record_type")
    List<JSONObject> getPayTotal() throws Exception;

    /**
     * 骑行总时间和平均时间
     */
    @Select("SELECT COUNT(*) AS `count`, SUM(during) AS `sum` " +
            "FROM basic_card_record " +
            "WHERE record_type = 0")
    JSONObject getDuringTotal() throws Exception;

    /**
     * 统计区间骑行时长和消费
     */
    @Select("SELECT SUM(during) AS `during`, SUM(money) AS `cost` " +
            "FROM basic_card_record " +
            "WHERE record_type = 0 " +
            "AND gmt_create BETWEEN #{beg} AND #{end}")
    JSONObject getDuringAndCost(@Param("beg") String beg,
                                @Param("end") String end) throws Exception;
}
